package dbLayer;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import modelLayer.ProductLine;
import modelLayer.Sale;

public class DBSales implements IFDBSales {

	protected Connection con;

	public DBSales() {
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public int insertSale(Sale sale) throws Exception {
		String query = "INSERT INTO invoices (totalPrice) VALUES('"
				+ sale.getTotal() + "');";
		query += "INSERT INTO sales (invoiceId) VALUES((SELECT MAX(invoiceId) from invoices));";
		Iterator itr = sale.getSaleList().iterator();
		while (itr.hasNext()) {
			ProductLine productLine = (ProductLine) itr.next();
			query += "INSERT INTO saleProducts(saleId, productId, quantity, subTotal) VALUES ((SELECT MAX(saleId) from sales), ";
			query += "'" + productLine.getProduct().getProductId() + "', ";
			query += "'" + productLine.getQuantity() + "', ";
			query += "'" + productLine.getSubTotal() + "');";
			query += "UPDATE products SET stockQuantity=stockQuantity - "
					+ productLine.getQuantity() + " WHERE productId='" + productLine.getProduct().getProductId() + "';";
		}
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Inserting Sale ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Sale Inserted!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

}
